package com.jiepos.mpos.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.core.dao.BaseDao;
import com.jiepos.mpos.core.util.IDFactoryUtil;

import oracle.jdbc.driver.OracleTypes;

/**
* 标题:
* 模块: 支付公共Dao
* 描 述: <描述>
* 版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
* @author 杨剑武
* @version 1.0
* @since 2017年10月25日
* @history（历次修订内容、修订人、修订时间等）
*/

@SuppressWarnings("rawtypes")
@Repository
public class AppPayDao extends BaseDao {
	
	/**
	 * 
	 * 接口: 根据业务编号查询业务主键
	 * 
	 * @return
	 * @since 2017年10月25日
	 */
	public Map<String, Object> getServiceUuid(String service_no) throws SQLException {
		StringBuilder builder = new StringBuilder("select service_uuid,service_st")
				.append(" from t_app_service_inf where service_no = '").append(service_no)
				.append("' and ").append(SystemConstant.REC_ST).append(" = '1'");
		log.info("根据业务编号查询业务主键sql=[{}]", builder.toString());
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (data != null && !data.isEmpty()) {
			return data.get(0);
		}
		return null;
	}

	/**
	 * 
	 * 接口: 根据业务参数编号查询业务参数主键
	 * 
	 * @return
	 * @since 2017年10月25日
	 */
	public Map<String, Object> getServiceParamUuid(String param_no, String service_uuid) throws SQLException {
		StringBuilder builder = new StringBuilder("select service_param_uuid,param_st")
				.append(" from t_app_service_param where param_no = '").append(param_no)
				.append("' and service_uuid = '").append(service_uuid).append("' and ").append(SystemConstant.REC_ST)
				.append(" = '1'");
		log.info("根据业务参数编号查询业务参数主键sql=[{}]", builder.toString());
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (data != null && !data.isEmpty()) {
			return data.get(0);
		}
		return null;
	}

	public Map<String, Object> getActOutNo(String bankcard_uuid,String settle_acct) throws SQLException {
			StringBuilder builder = new StringBuilder(
					"select bankcard_uuid, acct_nm, settle_acct,crd_exp,cvv2,phone from T_APP_CUSTOM_BANKCARD where ");
					if(!StringUtils.isEmpty(bankcard_uuid)){
						builder.append("bankcard_uuid = '").append(bankcard_uuid).append("'");
					}else{
						builder.append("settle_acct = '").append(settle_acct).append("'");
					}
					builder.append(" and rec_st = '1'");
			log.info("根据银行卡主键查询对应的卡信息 sql=[{}]", builder.toString());
			List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
			if (data != null && !data.isEmpty()) {
				return data.get(0);
			}
		return null;
	}
	
	/**
	 * 
	 * 接口: 查询卡号是否已存在
	 * @param act_out_no
	 * @return
	 * @since 2017年10月27日
	 */
	public boolean getActUuid(String act_out_no) throws SQLException{
		StringBuilder builder = new StringBuilder(
		       "select bankcard_uuid as count from T_APP_CUSTOM_BANKCARD where settle_acct = '").append(act_out_no).append("' and rec_st='1'");
		log.info("查询卡号是否已存在sql=[{}]",builder.toString());
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (data != null && !data.isEmpty()) {
			return true;
		}
		return false;
	}
	
	/**
	 * 
	 * 接口: 根据会员编号查询会员信息
	 * @param custom_no
	 * @return
	 * @since 2017年10月25日
	 */
	public Map<String, Object> getCustomInfo(String custom_no) throws SQLException {
		StringBuilder builder = new StringBuilder(
				"select custom_uuid,login_id ,user_st ,name, phone ,certificate_no ,realname_auth_st ,vip_st,pay_pass_word from ")
						.append(Table.T_APP_CUSTOM_INF).append(" where ").append("custom_no = '").append(custom_no)
						.append("' and rec_st = '1'");
		log.info("根据会员编号查询会员信息sql=[{}]", builder.toString());
		List<Map<String, Object>> list = jdbcTemplate.queryForList(builder.toString());
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 
	 * 接口: 根据会员编号查询关于会员对于合法机构、及业务机构
	 * @param custom_no
	 * @param service_uuid
	 * @param service_param_uuid
	 * @param cutomer_level
	 * @return
	 * @since 2017年10月23日
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getCustomInsInfo(final String uuid,final String custom_no,final String service_uuid,final String service_param_uuid,final String custom_level){
		Map<String, Object> dataMap = (Map)  jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{call package_mainbusiness.pro_custom_ins_info (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, uuid);//本地流水
				cs.setString(2, custom_no);// 客户编号
				cs.setString(3, service_uuid);//系统业务主键
				cs.setString(4, service_param_uuid);// 系统业务参数主键
				cs.setString(5, custom_level);// 客户级别
				cs.registerOutParameter(6, OracleTypes.VARCHAR);// 合法机构UUID
				cs.registerOutParameter(7, OracleTypes.VARCHAR);// 业务机构状态
				cs.registerOutParameter(8, OracleTypes.VARCHAR);// 业务机构UUID
				cs.registerOutParameter(9, OracleTypes.VARCHAR);// 业务机构状态
				cs.registerOutParameter(10, OracleTypes.DECIMAL);// 贷记卡最小手续费
				cs.registerOutParameter(11, OracleTypes.DECIMAL);// 贷记卡扣率
				cs.registerOutParameter(12, OracleTypes.DECIMAL);// 贷记卡最大手续费
				cs.registerOutParameter(13, OracleTypes.DECIMAL);// 贷记卡附加手续费
				cs.registerOutParameter(14, OracleTypes.VARCHAR);// 查询结果
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("ins_uuid", cs.getString(6));// 合法机构UUID
				map.put("ins_st", cs.getString(7));// 合法机构状态
				map.put("service_ins_uuid", cs.getString(8));// 业务机构UUID
				map.put("service_ins_st", cs.getString(9));// 业务机构状态
				map.put("credit_minfee", cs.getDouble(10));// 贷记卡最小手续费
				map.put("credit_rate", cs.getDouble(11));// 贷记卡扣率
				map.put("credit_maxfee", cs.getDouble(12));// 贷记卡最大手续费
				map.put("credit_attachfee", cs.getDouble(13));// 贷记卡附加手续费
				map.put("result", cs.getString(14));// 查询结果
				log.info("会员[{}]查询对应机构信息:[{}]",custom_no,map);
				return map;// 获取输出参数的值
			}
		});
		return dataMap;
	}
	
	
	/**
	 * 
	 * 接口: 更新会员参数
	 * @return
	 * @since 2017年10月23日
	 */
	public String updateCustomServiceParam(final String loc_trace_no,final String trans_dt,final String custom_uuid,final String ins_uuid,final String service_param_uuid,final double amt){
		String result = (String) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{call package_mainbusiness.pro_custom_service_param(?,?,?,?,?,?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, loc_trace_no);// 交易日期
				cs.setString(2, trans_dt);// 交易日期
				cs.setString(3, custom_uuid);//客户UUID
				cs.setString(4, ins_uuid);// 机构UUID
				cs.setString(5, service_param_uuid);// 会员业务参数主键
				cs.setDouble(6, amt);// 交易金额
				cs.registerOutParameter(7, OracleTypes.VARCHAR);// 注册输出参数的类型
				return cs;
			}
		}, new CallableStatementCallback<Object>() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(7);// 获取输出参数的值
			}
		});
		return result;
	}
	/**
	 * 
	 * 接口: 会员、机构限额结果
	 * @param custom_no 
	 * @param act_out_no
	 * @param startDate
	 * @param nowDate
	 * @param amt
	 * @param ins_uuid
	 * @param service_uuid
	 * @param service_param_uuid
	 * @param custom_level
	 * @param crd_nature
	 * @return
	 * @since 2017年10月23日
	 */
	@SuppressWarnings("unchecked")
	public Map<String,String> getTranLimitResult(final String loc_trace_no ,final String custom_no,final String act_out_no, final double amt, final String ins_uuid,
			final String service_uuid, final String service_param_uuid, final String custom_level,final String crd_nature) {
		Map<String,String> dataMap = (Map) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{call package_mainbusiness.pro_tran_Limit(?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, loc_trace_no);// 本地流水
				cs.setString(2, custom_no);// 会员编号
				cs.setString(3, act_out_no);// 交易账户
				cs.setDouble(4, amt);// 交易金额
				cs.setString(5, ins_uuid);// 机构UUID
				cs.setString(6, service_uuid);// 业务主键
				cs.setString(7, service_param_uuid);// 业务参数主键
				cs.setString(8, custom_level);// 会员级别
				cs.setString(9, crd_nature);// 卡性质
				cs.registerOutParameter(10, OracleTypes.VARCHAR);// 输出查询结果
				cs.registerOutParameter(11, OracleTypes.VARCHAR);// 结果描述
				return cs;
			}
		}, new CallableStatementCallback<Object>() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				Map<String,String> data = new HashMap<String,String>();
				data.put("result", cs.getString(10));
				data.put("result_desc", cs.getString(11));
				return data;// 获取输出参数的值
			}
		});
		return dataMap;
	}
	
	   
    /**
     * 
     * 接口: 查询是否存在的对应的业务类型
     * @param param
     * @param service_uuid
     * @return
     * @since 2017年10月30日
     */
	public boolean getTranType(String ins_uuid,String service_uuid,String service_param_uuid,String custom_level) throws SQLException {
			StringBuffer buffer = new StringBuffer("select ins_param_uuid  from t_app_ins_service_param where ");
			buffer.append("ins_uuid='");
			buffer.append(ins_uuid);
			buffer.append("' and ");
			buffer.append("service_uuid='");
			buffer.append(service_uuid);
			buffer.append("' and ");
			buffer.append("service_param_uuid='");
			buffer.append(service_param_uuid);
			buffer.append("' and ");
			buffer.append("custom_level='");
			buffer.append(custom_level);
			buffer.append("' and ");
			buffer.append("rec_st='1'");
			log.info("受理交易类型sql=[{}]", buffer.toString());
			List<Map<String, Object>> data = jdbcTemplate.queryForList(buffer.toString());
			if (data != null && !data.isEmpty()) {
				return true;
			}
		return false;
	}

	
	
	 /**客户提现申请变更客户余额和增加明细记录*/
	@SuppressWarnings("unchecked")
	public String updateAbstract(final String apply_no) {

		String code = (String) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{CALL PACKAGE_MEMBER.P_CUSTOM_ABSTRACT (?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, apply_no);// 设置输入参数的值	
				cs.registerOutParameter(2, OracleTypes.NUMBER);// 错误码
				
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				String rsp_code=String.valueOf(cs.getObject(2));								
				log.info("提现申请[{}]对应的信息:[{}]",apply_no,rsp_code);
				return rsp_code;// 获取输出参数的值
			}
		});
		return code;
	}
	
	/**
	 * 
	 * 接口: 根据会员UUID查询对应的收款账号信息
	 * @param custom_uuid
	 * @return
	 * @author 杨剑武
	 * @since 2017年10月23日
	 */
	public Map<String,Object> getCustomBankCardInfo(String custom_uuid) throws SQLException{
		StringBuilder  builder =  new StringBuilder("select bankcard_uuid,settle_acct,acct_nm,super_bank_nm,certificate_no from t_app_custom_bankcard where ");
		builder.append("custom_uuid = '" + custom_uuid + "' and ");
		builder.append("acct_attr = '0' and ");
		builder.append("audit_st = '1' and ");	
		builder.append("rec_st = '1' ");
		log.info("sql=[{}]",builder.toString());
		List<Map<String,Object>> dataList = jdbcTemplate.queryForList(builder.toString());
		
		if(dataList != null && !dataList.isEmpty()){
			return dataList.get(0);
		}
		return null;
	}
	
	/**
	 * 
	 * 接口: 充值成功之后，会员升级为VIP
	 * @param custom_uuid
	 * @since 2017年11月2日
	 */
	public void updateCustomVipSt(String custom_uuid) throws SQLException{
		Assert.notNull(custom_uuid);
		long timestamp = System.currentTimeMillis();
		log.info("sql=[{}],参数[{},{}]","update t_app_custom_inf set vip_st = '1',vip_auth_ts = ? where custom_uuid = ?",new Timestamp(timestamp),custom_uuid);
		jdbcTemplate.update("update t_app_custom_inf set vip_st = '1',vip_auth_ts = ? where custom_uuid = ?", new Object[] {new Timestamp(timestamp), custom_uuid});  
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String,String> checkWithdraw(final String login_id,final String custom_uuid,final String custom_level,
			final String service_uuid,final String service_param_uuid,final double trans_at,final String applyNo) {

		Map<String,String> result = (Map) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "{CALL package_cash_pro.pro_cash (?,?,?,?,?,?,?,?,?)}";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, login_id);// app登录账号
				cs.setString(2, custom_uuid);// 会员uuid
				cs.setString(3, custom_level);// 会员等级
				cs.setString(4, service_uuid);// 业务uuid
				cs.setString(5, service_param_uuid);// 业务参数uuid
				cs.setDouble(6, trans_at);// 申请金额
				cs.setString(7, applyNo);// 申请单号
				cs.registerOutParameter(8, OracleTypes.VARCHAR);// 错误码		
				cs.registerOutParameter(9, OracleTypes.VARCHAR);// 错误描述	
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				Map<String,String> data = new HashMap<String,String>();
				data.put("rsp_code", cs.getString(8));
				data.put("rsp_msg", cs.getString(9));
				return data;// 获取输出参数的值
			}
		});
		return result;
	}
	
	public Map<String,Object> getChannelBankcard(String bankcard_uuid, String channel_No) throws SQLException {

		StringBuilder builder = new StringBuilder(
				"select channel_bankcard_uuid,user_agree_id,pay_agree_id from t_app_inf_channel_bankcard where bankcard_uuid ='")
						.append(bankcard_uuid).append("' and channel_No ='").append(channel_No)
						.append("' and open_st ='").append(SystemConstant.INS_ST_1).append("' and rec_st ='")
						.append(SystemConstant.REC_ST_1).append("'");
		log.info("查询渠道银行卡 sql=[{}]", builder.toString());
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (!StringUtils.isEmpty(data) && !data.isEmpty()) {
			return data.get(0);
		}
		return null;
	}
	
	public void saveChannelBankcard(String bankcard_uuid, String channel_No,String user_agree_id,String pay_agree_id,String login_id) throws Exception {
		String sql = "insert into t_app_inf_channel_bankcard (channel_bankcard_uuid,bankcard_uuid,channel_No,user_agree_id,pay_agree_id,open_st,rec_st,rec_upd_usr_id,rec_crt_ts) values(?,?,?,?,?,?,?,?,?)";
		String uuid = IDFactoryUtil.getId();
		long timestamp = System.currentTimeMillis();
		log.info("保存渠道银行卡 sql=[{}],参数为[{},{},{},{},{},{},{},{},{}]", sql, uuid, bankcard_uuid, channel_No,user_agree_id,pay_agree_id, SystemConstant.INS_ST_1,
				SystemConstant.REC_ST_1,login_id, new Timestamp(timestamp));
		jdbcTemplate.update(sql, new Object[] { uuid, bankcard_uuid, channel_No,user_agree_id,pay_agree_id, SystemConstant.INS_ST_1,
				SystemConstant.REC_ST_1,login_id, new Timestamp(timestamp) });

	}
	
	/**
	 * 
	 * 接口: 根据随机数查询对于的key值
	 * @param random_number
	 * @return
	 * @throws SQLException
	 * @since 2017年12月5日
	 */
	public Map<String, Object> getLogClientKey(String random_number) throws SQLException {
		StringBuilder builder = new StringBuilder(
				"select pin_key,cvv2_key,rand_key,exp_dt_key from t_app_log_client_key where random_number ='")
						.append(random_number).append("' and rec_st ='").append(SystemConstant.REC_ST_1).append("'");
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (!StringUtils.isEmpty(data) && !data.isEmpty()) {
			return data.get(0);
		}
		return null;
	}
	
	/**
	 * 
	 * 接口: 查询短信数量
	 * @param custom_no
	 * @param trf_mchnt_cd
	 * @param time
	 * @return
	 * @throws SQLException
	 * @since 2018年1月3日
	 */
	public Map<String, Object> numberOfSMS(String custom_no, String trf_mchnt_cd,int time) throws SQLException {
		StringBuilder builder = new StringBuilder(
				"select count(loc_trace_no) as sumCount from T_APP_LOG_TRANS_JNLS t where")
		        .append(" t.loc_dt = to_char(sysdate,'yyyymmdd') and ")
		        .append("t.trf_mchnt_cd = '")
		        .append(trf_mchnt_cd)
				.append("' and is_send_rand = '1' and loc_ts ")
				.append("between  to_char(sysdate-numtodsinterval(")
				.append(time)
				.append(",'minute'),'HH24MISS') and to_char(sysdate,'HH24MISS')");
		log.info("[{}]会员时间段[{}]交易笔数查询sql=[{}]", custom_no,time, builder.toString());
		List<Map<String, Object>> data = jdbcTemplate.queryForList(builder.toString());
		if (!StringUtils.isEmpty(data) && !data.isEmpty()) {
			return data.get(0);
		}
		return null;
	}
	/**
	 * 
	 * 接口: 获取数据库时间
	 * @return
	 * @since 2018年1月17日
	 */
	public Map<String, Object> getSystemTime() throws Exception{
		String sql = "select to_char(sysdate,'yyyymmdd') as loc_dt,  to_char(sysdate,'HH24miss') as loc_ts from dual";
		List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);
		if (!StringUtils.isEmpty(data) && !data.isEmpty()) {
			return data.get(0);
		}else{
			throw new Exception("查询系统时间发生异常");
		}
	}
}
